﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;

namespace Human
{
    public partial class frm_bieutinhhinhcanbo_bvdktinh : Form
    {
        string tungay = "", denngay = "";
        string s_bophan="",user_dn="",ngay = "", user = "";
        private DataSet dsxml = new DataSet();
        private AccessData mDB = new AccessData();
        Language lan = new Language();
        public frm_bieutinhhinhcanbo_bvdktinh(string s_makp,string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
        }
        
        private void frm_bieutinhhinhcanbo_bvdktinh_Load(object sender, EventArgs e)
        {
            int i = int.Parse(DateTime.Now.Month.ToString());
            if (i == 1 || i == 2 || i == 3)
                nudQuy.Value = 1;
            else if (i == 4 || i == 5 || i == 6)
                nudQuy.Value = 2;
            else if (i == 7 || i == 8 || i == 9)
                nudQuy.Value = 3;
            else
                nudQuy.Value = 4;
            nudNam.Value = int.Parse(DateTime.Now.Year.ToString());
        }

        private void btnXem_Click(object sender, EventArgs e)
        {
            if (radMH.Checked) print(1);
            if (radMI.Checked) print(2);
            if (radExcel.Checked) print(3);
            btnThoat.Focus();
        }
        public void getngay()
        {
            if (nudNam.Text.Trim() != "")
            {
                switch (nudQuy.Text)
                {
                    case "1": tungay = nudNam.Text.Trim() + "-01-01";
                        denngay = nudNam.Text.Trim() + "-03-31";
                        break;
                    case "2": tungay = nudNam.Text.Trim() + "-03-31";
                        denngay = nudNam.Text.Trim() + "-06-30";
                        break;
                    case "3": tungay = nudNam.Text.Trim() + "-06-30";
                        denngay = nudNam.Text.Trim() + "-09-30";
                        break;
                    case "4": tungay = nudNam.Text.Trim() + "-09-30";
                        denngay = nudNam.Text.Trim() + "-12-31";
                        break;
                    default: tungay = nudNam.Text.Trim() + "-01-01";
                        denngay = nudNam.Text.Trim() + "-03-31";
                        break;
                }
            }
        }
        private void print(int j)
        {
            try
            {
                getngay();
                user = mDB.user;
                int i = int.Parse(nudQuy.Value.ToString());
                int nam = int.Parse(nudNam.Value.ToString());
                int ss = i * 3;
                string s = "28/" + ss.ToString().PadLeft(2, '0').ToString() + "/" + nam.ToString(), sql = "";

                sql = " SELECT '" + ss.ToString().PadLeft(2, '0').ToString() + "' as thang,'" + nam.ToString() + "' as nam,bp.tenbp,";
                sql += " case when substr(trim(bp.mabsoft),1,1)='P' then 'I. Các phòng' ";
                sql += " when substr(trim(bp.mabsoft),1,1)='L' then 'II. Khoa lâm sàng' ";
                sql += " when substr(trim(bp.mabsoft),1,1)='C' then 'III. Cận lâm sàng' ";
                sql += " when substr(trim(bp.mabsoft),1,1)='D' then 'IV. Y tế dự phòng'";
                sql += " when substr(trim(bp.mabsoft),1,1)='X' then 'V. Y tế xã'";
                sql += " when substr(trim(bp.mabsoft),1,1)='K'  then 'VI. Bộ phận khác' end as label,";
                sql += " count(l.manv) as tongso";


                sql += ",sum(case when bc.mabc in ('TD') and dt.manhom in ('01') then 1 else 0 end ) as sdh_y";
                sql += " ,sum(case when bc.mabc in ('TD') and dt.manhom in ('02') then 1 else 0 end ) as sdh_d";
                sql += " ,sum(case when bc.mabc in ('DH') and dt.manhom in ('01') then 1 else 0 end ) as dh_y";
                sql += " ,sum(case when bc.mabc in ('DH') and dt.manhom in ('02') then 1 else 0 end ) as dh_d";
                sql += " ,sum(case when bc.mabc in ('DH') and dt.manhom not in ('01','02') then 1 else 0 end ) as dh_k";
                sql += " ,sum(case when bc.mabc not in ('TD','DH') and dt.manhom in ('07') then 1 else 0 end ) as cnk_ys";
                sql += " ,sum(case when bc.mabc not in ('TD','DH') and dt.manhom in ('06') then 1 else 0 end ) as cnk_dieudg";
                sql += " ,sum(case when bc.mabc not in ('TD','DH') and dt.manhom in ('08') then 1 else 0 end ) as cnk_hs";
                sql += " ,sum(case when bc.mabc not in ('TD','DH') and dt.manhom in ('04') and dt.mactdt in ('009') then 1 else 0 end ) as cnk_ktvxn";
                sql += " ,sum(case when bc.mabc not in ('TD','DH') and dt.manhom in ('04') then 1 else 0 end ) as cnk_ktvy";
                sql += " ,sum(case when bc.mabc in ('TH') and dt.manhom in ('02') then 1 else 0 end) as cnk_duoctc,'' as cnk_k";
                // sql+=" ,sum(case when bc.mabc not in ('TD','DH') and dt.manhom not in ('07','06','08','04') then 1 else 0 end) as cnk_k";

                sql += " from " + user + ".llnv l inner join " + user + ".hopdong hd on l.manv=hd.manv";
                sql += " inner join " + user + ".bophan bp on  bp.madv=l.madv";
                sql += " left join " + user + ".dmcd cd on cd.ma=l.macd";
                sql += " left join " + user + ".bangcap bc on bc.mabangcap=l.td_nnghiep";
                sql += " left join " + user + ".mangach mn on hd.mangach=mn.ma";
                sql += " left join " + user + ".mahd mh on hd.loaihd=mh.mahd";
                sql += " left join " + user + ".ctdaotao dt on l.nnghiep=dt.mactdt";
                sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";
                sql += " where  (hd.ngaynghi is null or to_timestamp(hd.ngaynghi,'yyyy-mm-dd') > to_timestamp('" + denngay + "','yyyy-mm-dd')) ";
                sql += " and to_timestamp(hd.ngaydenct,'yyyy-mm-dd')<=to_timestamp('" + denngay + "','yyyy-mm-dd') ";
                sql += " and hd.lan=ml.lan";
                sql += " group by bp.tenbp,bp.sothutu,";
                sql += " case when substr(trim(bp.mabsoft),1,1)='P' then 'I. Các phòng' ";
                sql += " when substr(trim(bp.mabsoft),1,1)='L' then 'II. Khoa lâm sàng' ";
                sql += " when substr(trim(bp.mabsoft),1,1)='C' then 'III. Cận lâm sàng'";
                sql += " when substr(trim(bp.mabsoft),1,1)='D' then 'IV. Y tế dự phòng'";
                sql += " when substr(trim(bp.mabsoft),1,1)='X' then 'V. Y tế xã'";
                sql += " when substr(trim(bp.mabsoft),1,1)='K'  then 'VI. Bộ phận khác' end";
                sql += " order by bp.sothutu";
                dsxml = mDB.get_data(sql);
                dsxml.WriteXml("..\\xml\\h_tinhhinhcbbvdktinh.xml", XmlWriteMode.WriteSchema);//@"xmlbaocao\Danhsachdenghinangbacluong_quy.xml
                if (dsxml.Tables[0].Rows.Count != 0)
                {
                    foreach (DataRow dr in dsxml.Tables[0].Rows)
                    {
                        int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0;
                        c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                        c2 = dr["sdh_y"].ToString().Trim() != "" ? int.Parse(dr["sdh_y"].ToString()) : 0;
                        c3 = dr["sdh_d"].ToString().Trim() != "" ? int.Parse(dr["sdh_d"].ToString()) : 0;
                        c4 = dr["dh_y"].ToString().Trim() != "" ? int.Parse(dr["dh_y"].ToString()) : 0;
                        c5 = dr["dh_d"].ToString().Trim() != "" ? int.Parse(dr["dh_d"].ToString()) : 0;
                        c6 = dr["dh_k"].ToString().Trim() != "" ? int.Parse(dr["dh_k"].ToString()) : 0;
                        c7 = dr["cnk_ys"].ToString().Trim() != "" ? int.Parse(dr["cnk_ys"].ToString()) : 0;
                        c8 = dr["cnk_dieudg"].ToString().Trim() != "" ? int.Parse(dr["cnk_dieudg"].ToString()) : 0;
                        c9 = dr["cnk_hs"].ToString().Trim() != "" ? int.Parse(dr["cnk_hs"].ToString()) : 0;
                        c10 = dr["cnk_ktvxn"].ToString().Trim() != "" ? int.Parse(dr["cnk_ktvxn"].ToString()) : 0;
                        c11 = dr["cnk_ktvy"].ToString().Trim() != "" ? int.Parse(dr["cnk_ktvy"].ToString()) : 0;
                        c12 = dr["cnk_duoctc"].ToString().Trim() != "" ? int.Parse(dr["cnk_duoctc"].ToString()) : 0;

                        c13 = c1 - (c2 + c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12);

                        dr["cnk_k"] = c13.ToString();

                    }

                    //else if(j==2)
                    //    rpt.Printer(dsxml,"bieu_tinhhinhCCVCtheoquy.rpt","",1);
                    //else
                    //    p.exp_excel(dsxml,"bieu_tinhhinhCCVCtheoquy");
                    if (j == 1)
                    {
                        frmReport f = new frmReport(mDB, dsxml, "", "h_tinhhinhcbbvdktinh.rpt", true, 0,user_dn);
                        f.ShowDialog(this);
                    }
                }
                if (dsxml.Tables[0].Rows.Count != 0)
                {

                }
                else
                {
                    MessageBox.Show(lan.Change_language_MessageText("Chưa có dữ liệu!"),AccessData.Msg);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        private void radMI_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void nudQuy_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                nudNam.Focus();
        }

        private void nudNam_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                radMH.Focus();
        }

        private void radMH_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                btnXem.Focus();
        }

        private void radMI_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                btnXem.Focus();
        }

        private void radExcel_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                btnXem.Focus();
        }
    }
}